﻿using Blog190311.Models.DBModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Blog190311.Models.DAL
{
    public class ArticleService
    {
        /// <summary>
        /// 查询所有日记,用于前台首页的日记的显示
        /// </summary>
        /// <returns></returns>
        public static List<Article> GetArticleMain()
        {
            //创建一个article集合
            List<Article> list = new List<Article>();
            //定义SQL语句
            string sql = string.Format("select art .Id ,art .Title ,art .SimpleInfo ,art .MainPicUrl from Articles art inner join RecArticles rec on art.Id = rec.ArticleId where art.ShowType = '文章' and art.IsPub = '是' and rec.PosId = 3 order by rec.Weight desc");
            //调用DBHepler中获取多个值的方法，并传入sql
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            //用while循环遍历read读取器读取到的数据
            while (dr.Read())
            {
                //创建一个article对象并把读取到的数据添加到article对象中，然后吧=把article对象添加到article集合中
                list.Add(new Article()
                {
                    Id = Convert.ToInt32(dr["id"]),
                    MainPicUrl = dr["MainPicUrl"].ToString(),
                    Title = dr["Title"].ToString(),
                    SimpleInfo = dr["SimpleInfo"].ToString()
                });
            }
            //返回article集合
            return list;
        }

        /// <summary>
        /// 根据类型和标签查询日记信息以及查询所有日记信息
        /// </summary>
        /// <param name="Tags"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public List<Article> GetArticleByPage(int page, int limit, string title, string Tags, string Type)
        {
            //创建一个article集合
            List<Article> list = new List<Article>();
            //定义一个空sql语句
            string sqlWhere = "";
            if (!string.IsNullOrWhiteSpace(title))
            {
                sqlWhere += " and title like '%" + title + "%'";
            }
            if (!String.IsNullOrWhiteSpace(Tags))//判断标签
            {
                sqlWhere += string.Format(" and Tags = '{0}'", Tags);//拼接标签sql
            }
            if (!String.IsNullOrWhiteSpace(Type))//判断类型
            {
                sqlWhere += string.Format(" and type = '{0}'", Type);//拼接类型sql
            }
            //定义SQL语句
            string sql = string.Format("select top({1}) *from Articles where ShowType ='文章'and IsPub='是' and Id not in(select top({0}*{1}) Id from Articles where ShowType ='文章' and IsPub='是')", page - 1, limit);
            //拼接SQL语句
            sql = sql + sqlWhere + "order by CreateTime desc";
            //调用DBHelper中获取多个值的方法，并传入sql
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            //用while循环遍历read读取器读取到的数据
            while (dr.Read())
            {
                //创建一个article对象并把读取到的数据添加到article对象中，然后把article对象添加到article集合中
                list.Add(new Article()
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    Title = dr["Title"].ToString(),
                    SimpleInfo = dr["SimpleInfo"].ToString(),
                    Cont = dr["Cont"].ToString(),
                    MainPicUrl = dr["MainPicUrl"].ToString()
                });
            }
            //返回article集合
            return list;
        }

        public int GetCount(string title)
        {
            string sqlWhere = " where ShowType ='文章'";
            if (!string.IsNullOrWhiteSpace(title))
            {
                sqlWhere += " and title '%" + title + "%'";
            }
            string sql = "select COUNT(*) from Articles" + sqlWhere;
            object re = DBHelper.ExecuteScalar(sql);
            if (re!=null)
            {
                return Convert.ToInt32(re);
            }
            return 0;
        }

        /// <summary>
        /// 点击排行
        /// </summary>
        /// <returns></returns>
        public static List<Article> GetArticleClick()
        {
            List<Article> list = new List<Article>();
            string sql = "select top 8 * from Articles where ShowType='文章' and IsPub = '是' order by readtimes desc";
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                list.Add(new Article()
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    Title = dr["Title"].ToString()
                });
            }
            return list;
        }

        /// <summary>
        /// 标签云
        /// </summary>
        /// <returns></returns>
        public static List<Article> GetArticleCloud()
        {
            List<Article> list = new List<Article>();
            string sql = "select distinct Tags from [dbo].[Articles] where ShowType='文章' and IsPub = '是'";
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                list.Add(new Article()
                {
                    Tags = dr["Tags"].ToString()
                });
            }
            return list;
        }

        /// <summary>
        /// 文章分类
        /// </summary>
        /// <returns></returns>
        public static List<Article> GetArticleWord()
        {
            List<Article> list = new List<Article>();
            string sql = "select Type,count(type) as count from Articles where ShowType='文章' and IsPub='是' group by Type";
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                list.Add(new Article()
                {
                    Type = dr["type"].ToString(),
                    Count = Convert.ToInt32(dr["count"])
                });
            }
            return list;
        }

        /// <summary>
        /// 站长推荐
        /// </summary>
        /// <returns></returns>
        public static List<Article> GetArticleTuiJian()
        {
            List<Article> list = new List<Article>();
            string sql = "select art .Id ,art .Title ,art .SimpleInfo ,art .MainPicUrl from Articles art inner join RecArticles rec on art.Id = rec.ArticleId where art .ShowType = '文章' and art .IsPub = '是' and rec .PosId = 2 order by rec.Weight desc";
            SqlDataReader dr = DBHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                list.Add(new Article()
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    MainPicUrl = dr["MainPicUrl"].ToString(),
                    Title = dr["Title"].ToString(),
                    SimpleInfo = dr["SimpleInfo"].ToString()
                });
            }
            return list;
        }

        /// <summary>
        /// 通过id查询文章详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article GetArticles(int id)
        {
            //创建一个article对象
            Article articles = new Article();
            //定义SQL语句
            string sql = string.Format("select * from Articles where ShowType='文章' and IsPub = '是' and Id={0}", id);
            //调用DBHepler中获取多个值的方法，传入SQL
            using (SqlDataReader dr = DBHelper.ExecuteReader(sql))
            {
                //如果读取器中有数据
                if (dr.Read())
                {
                    //把数据添加到article对象中
                    articles.Id = Convert.ToInt32(dr["Id"]);
                    articles.Title = dr["Title"].ToString();
                    articles.ReadTimes = Convert.ToInt32(dr["ReadTimes"]);
                    articles.Tags = dr["Tags"].ToString();
                    articles.SimpleInfo = dr["SimpleInfo"].ToString();
                    articles.Cont = dr["Cont"].ToString();
                    articles.MainPicUrl = dr["MainPicUrl"].ToString();
                    articles.Source = dr["Source"].ToString();
                    articles.Type = dr["Type"].ToString();
                    articles.CreateTime = dr["CreateTime"].ToString();
                    articles.PubTime = dr["PubTime"].ToString();
                    articles.IsPub = dr["IsPub"].ToString();
                    articles.ShowType = dr["ShowType"].ToString();
                }
            }
            //返回article对象
            return articles;
        }

        /// <summary>
        /// 下一页
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article GetArticleByIdNext(int id)
        {
            string sql = string.Format("select top 1*from Articles where Id>{0} and ShowType ='文章' and IsPub ='是' order by Id", id);
            using (SqlDataReader dr = DBHelper.ExecuteReader(sql))
            {
                if (dr.Read())
                {
                    Article article = new Article();
                    article.Id = Convert.ToInt32(dr["Id"]);
                    article.Title = dr["Title"].ToString();
                    article.ReadTimes = Convert.ToInt32(dr["ReadTimes"]);
                    article.Tags = dr["Tags"].ToString();
                    article.SimpleInfo = dr["SimpleInfo"].ToString();
                    article.Cont = dr["Cont"].ToString();
                    article.MainPicUrl = dr["MainPicUrl"].ToString();
                    article.Source = dr["Source"].ToString();
                    article.Type = dr["Type"].ToString();
                    article.CreateTime = dr["CreateTime"].ToString();
                    article.PubTime = dr["PubTime"].ToString();
                    article.IsPub = dr["IsPub"].ToString();
                    article.ShowType = dr["ShowType"].ToString();
                    return article;
                }
            }
            return null;
        }

        /// <summary>
        /// 上一页
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article GetArticleByIdPrev(int id)
        {
            string sql = string.Format("select top 1*from Articles where Id<{0} and ShowType ='文章' and IsPub ='是' order by Id desc", id);
            using (SqlDataReader dr = DBHelper.ExecuteReader(sql))
            {
                if (dr.Read())
                {
                    Article article = new Article();
                    article.Id = Convert.ToInt32(dr["Id"]);
                    article.Title = dr["Title"].ToString();
                    article.ReadTimes = Convert.ToInt32(dr["ReadTimes"]);
                    article.Tags = dr["Tags"].ToString();
                    article.SimpleInfo = dr["SimpleInfo"].ToString();
                    article.Cont = dr["Cont"].ToString();
                    article.MainPicUrl = dr["MainPicUrl"].ToString();
                    article.Source = dr["Source"].ToString();
                    article.Type = dr["Type"].ToString();
                    article.CreateTime = dr["CreateTime"].ToString();
                    article.PubTime = dr["PubTime"].ToString();
                    article.IsPub = dr["IsPub"].ToString();
                    article.ShowType = dr["ShowType"].ToString();
                    return article;
                }
            }
            return null;
        }
        ///<summary>
        ///通过Id获取文章内容
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns> 
        public Article GetArticleById(int id)
        {
            string sql = string.Format("select *from Articles where Id={0}", id);
            using (SqlDataReader dr = DBHelper.ExecuteReader(sql))
            {
                if (dr.Read())
                {
                    Article article = new Article();
                    article.Id = Convert.ToInt32(dr["Id"]);
                    article.Title = dr["Title"].ToString();
                    article.ReadTimes = Convert.ToInt32(dr["ReadTimes"]);
                    article.Tags = dr["Tags"].ToString();
                    article.SimpleInfo = dr["SimpleInfo"].ToString();
                    article.Cont = dr["Cont"].ToString();
                    article.MainPicUrl = dr["MainPicUrl"].ToString();
                    article.Source = dr["Source"].ToString();
                    article.Type = dr["Type"].ToString();
                    article.CreateTime = dr["CreateTime"].ToString();
                    article.PubTime = dr["PubTime"].ToString();
                    article.IsPub = dr["IsPub"].ToString();
                    article.ShowType = dr["ShowType"].ToString();
                    return article;
                }
            }
            return null;
        }
    }
}